package swd.basic.table_name_maintain;

import com.pub.database.*;
import java.util.*;


public class Business {
   QueryBean gqbnDAO=null;
   public Business(QueryBean pqbnDAO){
      gqbnDAO=pqbnDAO;
   }
   String selectTableDataToFld(View view) throws Exception {
      String lstrSQL=null;
      Hashtable<String,Object> lhtTableData=null;
      try{
         lstrSQL="select dbf_no,sys_no,dbf_name from wkf_table where dbf_no='" + view.getdbf_no() + "'";
         lhtTableData=gqbnDAO.readSingleRowData(lstrSQL);
         if(lhtTableData==null){
            return "查無資料";
         }
         view.setdbf_no(String.valueOf((lhtTableData.get("DBF_NO").toString())));
         view.setsys_no(String.valueOf((lhtTableData.get("SYS_NO").toString())));
         view.setdbf_name(String.valueOf((lhtTableData.get("DBF_NAME").toString())));

         return "";
      }
      catch(Exception e){
         throw new Exception(e.toString());
      }
      finally{
         lhtTableData.clear();
         lhtTableData=null;
      }
   }
   String exeUpdSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(view.getdbf_no()==null ? null :String.valueOf(view.getdbf_no()));
         lvtrParam.add(view.getsys_no()==null ? null :String.valueOf(view.getsys_no()));
         lvtrParam.add(view.getdbf_name()==null ? null :String.valueOf(view.getdbf_name()));
         lstrSQL="update wkf_table set dbf_no=?,sys_no=?,dbf_name=? where dbf_no='" + view.getdbf_no() + "'";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            return "wkf_table";
         }

         saveDataToWkf_Sys_Table(view.getsys_no(),view.getdbf_no());
         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e.toString());
      }
      finally{
         lvtrParam.clear();
      }
   }
   String exeInsSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getdbf_no()==null ? null : view.getdbf_no()));
         lvtrParam.add((view.getdbf_name()==null ? null : view.getdbf_name()));
         lvtrParam.add((view.getsys_no()==null ? null : view.getsys_no()));
         lstrSQL="insert into wkf_table(dbf_no,dbf_name,sys_no) values (?,?,?)";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){;
            return "wkf_table";
         }
         lvtrParam.clear();

         saveDataToWkf_Sys_Table(view.getsys_no(),view.getdbf_no());

         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }

   boolean exeUpdSQL(View view,int pintSn){
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(view.getsys_no_g()[pintSn]==null ? null :String.valueOf(view.getsys_no_g()[pintSn]));
         lvtrParam.add(view.getdbf_name_g()[pintSn]==null ? null :String.valueOf(view.getdbf_name_g()[pintSn]));
         lstrSQL="update wkf_table set sys_no=?,dbf_name=? where dbf_no='" + view.getdbf_no_g()[pintSn] + "'";

         saveDataToWkf_Sys_Table(view.getsys_no_g()[pintSn],view.getdbf_no_g()[pintSn]);

         return gqbnDAO.executeSQL(lstrSQL,lvtrParam);
      }
      catch(Exception e){
         e.printStackTrace();
         return false;
      }
      finally{
         lvtrParam.clear();
      }
   }


   void exeDelSQL(View view,int pintSn){
      String lstrSQL=new String();
      try{
         lstrSQL="delete wkf_table " +
                 " where dbf_no='" + view.getdbf_no_g()[pintSn] + "'";
         gqbnDAO.executeSQL(lstrSQL);
         // 刪除舊資料
         exeDelWkfSysTableSQL(view.getsys_no_g()[pintSn],view.getdbf_no_g()[pintSn]);
      } catch(Exception e){
         e.printStackTrace();
      } 
   }

   String batchUpdateData(View view) throws Exception{
       String[] lstrRowSn=view.getrowsn().split(",");

       try{
           for(int i=0;i<lstrRowSn.length;i++){
               if(!exeUpdSQL(view,Integer.parseInt(lstrRowSn[i]))){
                   throw new Exception("update error");
               }
           }
           return "儲存成功!";
       }
       catch(Exception e){
           return "儲存失敗!!";
       }
   }

   void saveDataToWkf_Sys_Table(String strSysNo,String strDbfNo) throws Exception{
      // 檢查資料庫中是否已經有資料,沒有則新增資料至表格中
      String strQty="0";
      strQty = (gqbnDAO.readSingleValueData("select count(*) from wkf_sys_table " +
                                            " where upper(sys_no) = '" + strSysNo.toUpperCase() + "'" +
                                            "   and upper(dbf_no) = '" + strDbfNo.toUpperCase() + "'"));
      if (strQty.equals("0")){
         exeInsWkfSysTableSQL(strSysNo,strDbfNo);
      }
   }

   void exeInsWkfSysTableSQL(String strSysNo,String strDbfNo) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(strSysNo);
         lvtrParam.add(strDbfNo);
         lvtrParam.add("0001");
         lstrSQL="insert into wkf_sys_table(sys_no,dbf_no,seqno) values (?,?,?)";
         gqbnDAO.executeSQL(lstrSQL,lvtrParam);
         lvtrParam.clear();
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }

   void exeDelWkfSysTableSQL(String strSysNo,String strDbfNo){
      String lstrSQL=new String();
      try{
         lstrSQL="delete wkf_sys_table " +
                 " where upper(sys_no) = '" + strSysNo.toUpperCase() + "'" +
                 "   and upper(dbf_no) = '" + strDbfNo.toUpperCase() + "'";
         gqbnDAO.executeSQL(lstrSQL);
      } catch(Exception e){
         e.printStackTrace();
      }
   }


   String checkHaveTableData(View view) throws Exception{
      // 檢查資料庫中是否已經有此表格
      String strQty="0";
      strQty = (gqbnDAO.readSingleValueData("select count(dbf_no) from wkf_table " +
                                            " where upper(dbf_no)='" + view.getdbf_no().toUpperCase() + "'"));
      return strQty;
   }

   void setDataViewForm(DataViewForm DataViewForm,View view,String pstrLoginUser) throws Exception{

       //StringBuffer stbCondition = new StringBuffer();
       StringBuffer stbPageActionURI = new StringBuffer("table_name_maintain.jsp?rowsn=");
       String sqlcondition="";
       DataViewForm.setFieldsList(" '',dbf_no,dbf_name,sys_no");
       DataViewForm.setTablesList(" wkf_table  ");
       sqlcondition=(view.getdbf_no()==null && view.getsys_no()==null && view.getdbf_name()==null ? "1!=1":" 1=1 ");
       sqlcondition=view.getdbf_no()==null ? sqlcondition :sqlcondition+" and dbf_no like '"+view.getdbf_no()+ "%'"  ;
       sqlcondition=view.getsys_no()==null ? sqlcondition :sqlcondition+" and sys_no= '"+view.getsys_no() + "'"  ;
       sqlcondition=view.getdbf_name()==null ? sqlcondition :sqlcondition+" and dbf_name like  '%"+view.getdbf_name()+ "%'" ;
       DataViewForm.setSQLCondition(sqlcondition);

       DataViewForm.setOrderByFields("dbf_no");
       DataViewForm.setOrderBySeq("asc");
       DataViewForm.setGroupBy(null);
       DataViewForm.setHaving(null);

       String strColRelation[][] = new String[5][];

       strColRelation[0] = new String[5];
       strColRelation[1] = new String[8];
       strColRelation[2] = new String[5];
       strColRelation[3] = new String[5];
       strColRelation[4] = new String[5];

       strColRelation[0][0] = "''";
       strColRelation[0][1] = "INPUT";
       strColRelation[0][2] = "WIDTH='10%' align='center'";
       strColRelation[0][3] = "<INPUT class='button b_normal' TYPE=\"BUTTON\" onclick=\"javascript:saveRowSn('%sn%');document.all.item('prgAction').value='delete';document.forms[0].submit();\" NAME=\"del\" VALUE=";
       strColRelation[0][4] = "'刪除'";

       strColRelation[1][0]="'檔案編號'";
       strColRelation[1][1]="LINK";
       strColRelation[1][2]=" nowrap WIDTH='30%' ";
       strColRelation[1][3]="<a target='_blank' href=";
       strColRelation[1][4]="1";
       strColRelation[1][5]="'table_schema_maintain.jsp'";
       strColRelation[1][6]="dbf_no=";
       strColRelation[1][7]="1";

       strColRelation[2][0]="'<IMG SRC=\"/PUBPAGE/pub_images/star.gif\" WIDTH=\"10\" HEIGHT=\"10\" HSPACE=\"2\" alt=\"\"/>檔案名稱'";
       strColRelation[2][1]="INPUT";
       strColRelation[2][2]="WIDTH='50%'";
       strColRelation[2][3]="<INPUT TYPE='text' size='40' MAXLENGTH='40' name='dbf_name_g' onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input' VALUE=";
       strColRelation[2][4]="2";

       strColRelation[3][0]="'<IMG SRC=\"/PUBPAGE/pub_images/star.gif\" WIDTH=\"10\" HEIGHT=\"10\" HSPACE=\"2\" alt=\"\"/>系統編號'";
       strColRelation[3][1]="INPUT";
       strColRelation[3][2]="WIDTH='10%'";
       strColRelation[3][3]="<INPUT TYPE='text' size='10' MAXLENGTH='10' name='sys_no_g'  onDblClick=\"javascript:fc_get_sys_data(this);saveRowSn('%sn%');\" onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input_window hand' VALUE=";
       strColRelation[3][4]="3";

       strColRelation[4][0]="HIDDEN";
       strColRelation[4][1]="INPUT";
       strColRelation[4][2]="WIDTH='0%'";
       strColRelation[4][3]="<INPUT TYPE='HIDDEN' size='8' name='dbf_no_g' VALUE=";
       strColRelation[4][4]="1";

       DataViewForm.setColRelation(strColRelation);//將欄位相關設定殖入ActionForm
       DataViewForm.setJumpPageButtonShow(false);   //是否要秀出上下頁按鈕
       DataViewForm.setPageBtnOnTop(false);         //將button and rows info 秀在頂端
       DataViewForm.setPageBtnOnButtom(true);      //將button and rows info 秀在底端
       DataViewForm.setFilterShow(false);           //是否要秀出過濾選擇欄位
       DataViewForm.setJumpPageShow(false);         //是否要秀出跳頁下拉式選單
       DataViewForm.setRowsInfoShow(false);         //是否要秀出筆數資訊
       DataViewForm.setRowPageShow(false);          //是否要秀出每頁筆數控制欄
       DataViewForm.setExcelShow(false);           //是否要秀出轉Excel button
       DataViewForm.setOrderEnable(false);          //是否要提供排序點選功能
       DataViewForm.setShowHeaderWhenNoData(false);//當ResultSet無資料是否要秀Header

       DataViewForm.setTableHeaderHtml("class='td_head_grid' align='center'  width='680px'");
             //設定上一頁與下一頁按鈕的form action(通常為母頁即本頁)
       DataViewForm.setPageActionURI(stbPageActionURI.toString());





    // if (DataViewForm!=null &&  DataViewForm.getTotalDataSize()<=0){
        // view.setBtnSaveAllShow("none");
   //  }
   }


   String checkNotNullFld(View view) throws Exception{
      StringBuffer lstbNullFldList=new StringBuffer();
      return (lstbNullFldList.toString().equals("") ? "" : lstbNullFldList.delete(lstbNullFldList.length()-1,lstbNullFldList.length()).toString());
   }
   void setInitValueForAdd(View view,String pstrUserID) throws Exception{
   }
   void setReadonlyForAdd(View view) throws Exception{
   }

}
